Data Transformation and Relationships 9

Step 1: Click on 'Transform Data' to open Power Query Editor (See screenshot)



Step 2: Right click on the Start_Time column then click 'Duplicate Column' (See GIF below)



Step 3: Right click on 'Start_time - Copy', scroll down to 'Transform', then click on 'Time Only'. Scroll across to locate 'Start_Time, right click on 'Start_Time, scroll down to 'Transform', then click on 'Date Only' (See GIF below).



Step 4: Next step is to Group Severity. Severity is grouped from 0 - 5. For this Data, we will group 0,1 as Low Severity, 2 as Average Severity and 3,4 as High Severity.

  1. Right Click on 'Severity', then click on 'Replace values'. Type '0' in 'Value to find' and type '1' in 'Replace With', then click 'OK'.
  2. Right Click on 'Severity' again, then click on ''Replace values', this time type '4' in 'Value to find' and type '3' in 'Replace With' then click 'OK'. (See GIF below).



Step 5: Now it's time to clean our Data and Remove unused columns.

  1. Right click on Start_Time, then click on Rename. Rename Start_Time to Start_Date. Repeat this step for Start_Time - Copy, rename Start_Time - Copy to Start_Time.
  2. To Remove a column, right click on the column then click 'Remove' (See GIF below). Remove all columns except the following:

Click 'Close & Apply' after removing all unused columns.



Step 6:  Click on the ellipses for the US_Accidents_Dec20 Table then click on New column. Copy and paste DAX equation below, then click  (See screenshot and GIF below)

 TimeKey = FORMAT(US_Accidents_Dec20[Start_Time], "hhmm")





Step 7: Now it's time to create relationships between both tables. Click on the Model icon to show Data Model. Select 'Date' column on the 'Date' Table then drag to the 'Start_Date' column on the 'US_Accident_Dec20' table. Select 'TimeKey' on the 'Time' table the drag to 'TimeKey' on the 'US_Accident_Dec20' table. (See GIF below).




Clean, transform, and load data in Power BI: https://docs.microsoft.com/en-us/learn/modules/clean-data-power-bi/

Create and manage relationships in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships